#! /bin/bash
export LANG=zh_CN.UTF-8
PRESTO_HOME=/export/server/presto/bin/presto


${PRESTO_HOME} --catalog hive --server 192.168.88.80:8090 --execute "
--需求1 每天/每月/每年各个校区的报名人数
insert into zxedu_rpt.signup_dt_school
select
dt,
school_id,
schoool_name,
signup_count
from zxedu_dws.dws_signup_count
where group_type = '日期，校区';
insert into zxedu_rpt.signup_month_school
select
month_code,
school_id,
schoool_name,
signup_count
from zxedu_dws.dws_signup_count
where group_type = '月份，校区';
insert into zxedu_rpt.signup_year_school
select
year_code,
school_id,
schoool_name,
signup_count
from zxedu_dws.dws_signup_count
where group_type = '年份，校区';
--需求2 每天/每月/每年线上线下各个校区的报名人数
insert into zxedu_rpt.signup_dt_origintype_school
select
dt,
origin_type,
school_id,
schoool_name,
signup_count
from zxedu_dws.dws_signup_count
where group_type = '日期，校区，线上线下';
insert into zxedu_rpt.signup_mongth_origintype_school
select
month_code,
origin_type,
school_id,
schoool_name,
signup_count
from zxedu_dws.dws_signup_count
where group_type = '月份，校区，线上线下';
insert into zxedu_rpt.signup_year_origintype_school
select
year_code,
origin_type,
school_id,
schoool_name,
signup_count
from zxedu_dws.dws_signup_count
where group_type = '年份，校区，线上线下';

--需求6 每天/每月/每年线上线下各个咨询中心的报名人数
insert into zxedu_rpt.signup_dt_origintype_tdepart
select
dt,
origin_type,
tdepart_id,
tdepart_name,
signup_count
from zxedu_dws.dws_signup_count
where group_type = '日期，线上线下，咨询中心';

insert into zxedu_rpt.signup_month_origintype_tdepart
select
month_code,
origin_type,
tdepart_id,
tdepart_name,
signup_count
from zxedu_dws.dws_signup_count
where group_type = '月份，线上线下，咨询中心';

insert into zxedu_rpt.signup_year_origintype_tdepart
select
year_code,
origin_type,
tdepart_id,
tdepart_name,
signup_count
from zxedu_dws.dws_signup_count
where group_type = '年份，线上线下，咨询中心';

--需求7 每天/每月/每年线上线下的意向转报名率 = 报名人数 / 意向人数
insert into zxedu_rpt.signup_dt_origintype_relationship
select
dt,
origin_type,
cast((signup_count * 1.0000/(dws_signup_count.relationship_count) *100) as decimal (5,2)) as rate_signup_relationship
from zxedu_dws.dws_signup_count
where group_type = '日期，线上线下';

insert into zxedu_rpt.signup_month_origintype_relationship
select
month_code,
origin_type,
cast((signup_count * 1.0000/(dws_signup_count.relationship_count) *100) as decimal (5,2)) as rate_signup_relationship
from zxedu_dws.dws_signup_count
where group_type = '月份，线上线下';

insert into zxedu_rpt.signup_year_origintype_relationship
select
year_code,
origin_type,
cast((signup_count * 1.0000/(dws_signup_count.relationship_count) *100) as decimal (5,2)) as rate_signup_relationship
from zxedu_dws.dws_signup_count
where group_type = '年份，线上线下';

--需求8 每天/每月/每年线上的有效线索报名转换率 = 报名人数 / 有效线索人数
insert into zxedu_rpt.signup_dt_origintype_effectiveclue
select
dt,
origin_type,
cast((signup_count * 1.0000/(dws_signup_count.effective_clue_count) *100) as decimal (5,2)) as rate_signup_effectiveclue
from zxedu_dws.dws_signup_count
where group_type = '日期，线上线下'
and origin_type = 'online';

insert into zxedu_rpt.signup_month_origintype_effectiveclue
select
month_code,
origin_type,
cast((signup_count * 1.0000/(dws_signup_count.effective_clue_count) *100) as decimal (5,2)) as rate_signup_effectiveclue
from zxedu_dws.dws_signup_count
where group_type = '月份，线上线下'
and origin_type = 'online';

insert into zxedu_rpt.signup_year_origintype_effectiveclue
select
year_code,
origin_type,
cast((signup_count * 1.0000/(dws_signup_count.effective_clue_count) *100) as decimal (5,2)) as rate_signup_effectiveclue
from zxedu_dws.dws_signup_count
where group_type = '年份，线上线下'
and origin_type = 'online';
"